using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using RealState.BusinessObjects;

namespace RealState.DAL
{
	public class NewsDAO
	{
		public NewsDAO()
		{
			DbProviderHelper.GetConnection();
		}
		public List<News> GetNewss()
		{
			try
			{
				List<News> lstNewss = new List<News>();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECTNewss",CommandType.StoredProcedure);
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					News oNews = new News();
                    oNews.RowNum = Convert.ToInt32(oDbDataReader["RowNum"]);
					oNews.Id = Convert.ToInt32(oDbDataReader["Id"]);
					oNews.Title = Convert.ToString(oDbDataReader["Title"]);
					oNews.Content = Convert.ToString(oDbDataReader["Content"]);
                    oNews.Description = Convert.ToString(oDbDataReader["Description"]);

					if(oDbDataReader["CategoryId"] != DBNull.Value)
						oNews.CategoryId = Convert.ToInt32(oDbDataReader["CategoryId"]);

					if(oDbDataReader["UserId"] != DBNull.Value)
						oNews.UserId = Convert.ToInt32(oDbDataReader["UserId"]);

                    if (oDbDataReader["CreatedTime"] != DBNull.Value)
                        oNews.CreatedTime = Convert.ToDateTime( oDbDataReader["CreatedTime"]); 

					if(oDbDataReader["IsActive"] != DBNull.Value)
						oNews.IsActive = Convert.ToBoolean(oDbDataReader["IsActive"]);

					if(oDbDataReader["IsRemove"] != DBNull.Value)
						oNews.IsRemove = Convert.ToBoolean(oDbDataReader["IsRemove"]);

					if(oDbDataReader["ImgPath"] != DBNull.Value)
						oNews.ImgPath = Convert.ToString(oDbDataReader["ImgPath"]);
                    oNews.IsNews = Convert.ToBoolean(oDbDataReader["IsNews"]);
					lstNewss.Add(oNews);
				}
				oDbDataReader.Close();
				return lstNewss;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
        public List<News> GetNewss_2()
        {
            try
            {
                List<News> lstNewss = new List<News>();
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECTNewss_2", CommandType.StoredProcedure);
                DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
                while (oDbDataReader.Read())
                {
                    News oNews = new News();
                    oNews.RowNum = Convert.ToInt32(oDbDataReader["RowNum"]);
                    oNews.Id = Convert.ToInt32(oDbDataReader["Id"]);
                    oNews.Title = Convert.ToString(oDbDataReader["Title"]);
                    oNews.Content = Convert.ToString(oDbDataReader["Content"]);
                    oNews.Description = Convert.ToString(oDbDataReader["Description"]);

                    if (oDbDataReader["CategoryId"] != DBNull.Value)
                        oNews.CategoryId = Convert.ToInt32(oDbDataReader["CategoryId"]);

                    if (oDbDataReader["UserId"] != DBNull.Value)
                        oNews.UserId = Convert.ToInt32(oDbDataReader["UserId"]);

                    if (oDbDataReader["CreatedTime"] != DBNull.Value)
                        oNews.CreatedTime = Convert.ToDateTime(oDbDataReader["CreatedTime"]);

                    if (oDbDataReader["IsActive"] != DBNull.Value)
                        oNews.IsActive = Convert.ToBoolean(oDbDataReader["IsActive"]);

                    if (oDbDataReader["IsRemove"] != DBNull.Value)
                        oNews.IsRemove = Convert.ToBoolean(oDbDataReader["IsRemove"]);

                    if (oDbDataReader["ImgPath"] != DBNull.Value)
                        oNews.ImgPath = Convert.ToString(oDbDataReader["ImgPath"]);
                    oNews.IsNews = Convert.ToBoolean(oDbDataReader["IsNews"]);
                    lstNewss.Add(oNews);
                }
                oDbDataReader.Close();
                return lstNewss;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
		public News GetNews(int Id)
		{
			try
			{
				News oNews = new News();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECTNews",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Id",DbType.Int32,Id));
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					oNews.Id = Convert.ToInt32(oDbDataReader["Id"]);
					oNews.Title = Convert.ToString(oDbDataReader["Title"]);
					oNews.Content = Convert.ToString(oDbDataReader["Content"]);
                    oNews.Description = Convert.ToString(oDbDataReader["Description"]);

					if(oDbDataReader["CategoryId"] != DBNull.Value)
						oNews.CategoryId = Convert.ToInt32(oDbDataReader["CategoryId"]);

					if(oDbDataReader["UserId"] != DBNull.Value)
						oNews.UserId = Convert.ToInt32(oDbDataReader["UserId"]);

                    if (oDbDataReader["CreatedTime"] != DBNull.Value)
                        oNews.CreatedTime = Convert.ToDateTime( oDbDataReader["CreatedTime"]);

					if(oDbDataReader["IsActive"] != DBNull.Value)
						oNews.IsActive = Convert.ToBoolean(oDbDataReader["IsActive"]);

					if(oDbDataReader["IsRemove"] != DBNull.Value)
						oNews.IsRemove = Convert.ToBoolean(oDbDataReader["IsRemove"]);

					if(oDbDataReader["ImgPath"] != DBNull.Value)
						oNews.ImgPath = Convert.ToString(oDbDataReader["ImgPath"]);
                    oNews.IsNews = Convert.ToBoolean(oDbDataReader["IsNews"]);
				}
				oDbDataReader.Close();
				return oNews;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int AddNews(News oNews)
		{
			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERTNews",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Title",DbType.String,oNews.Title));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Content",DbType.String,oNews.Content));
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, oNews.Description));
				if (oNews.CategoryId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CategoryId",DbType.Int32,oNews.CategoryId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CategoryId",DbType.Int32,DBNull.Value));
				if (oNews.UserId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@UserId",DbType.Int32,oNews.UserId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@UserId",DbType.Int32,DBNull.Value));
                if (oNews.CreatedTime.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreatedTime", DbType.DateTime, oNews.CreatedTime));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreatedTime", DbType.DateTime, DBNull.Value));

				if (oNews.IsActive.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsActive",DbType.Boolean,oNews.IsActive));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsActive",DbType.Boolean,DBNull.Value));
				if (oNews.IsRemove.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsRemove",DbType.Boolean,oNews.IsRemove));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsRemove",DbType.Boolean,DBNull.Value));
				if (oNews.ImgPath!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ImgPath",DbType.String,oNews.ImgPath));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ImgPath",DbType.String,DBNull.Value));
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsNews", DbType.Boolean, oNews.IsNews));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int UpdateNews(News oNews)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATENews",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Title",DbType.String,oNews.Title));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Content",DbType.String,oNews.Content));
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, oNews.Description));
				if (oNews.CategoryId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CategoryId",DbType.Int32,oNews.CategoryId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CategoryId",DbType.Int32,DBNull.Value));
				if (oNews.UserId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@UserId",DbType.Int32,oNews.UserId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@UserId",DbType.Int32,DBNull.Value));
				if (oNews.CreatedTime.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreatedTime",DbType.DateTime,oNews.CreatedTime));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreatedTime",DbType.DateTime,DBNull.Value));
				if (oNews.IsActive.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsActive",DbType.Boolean,oNews.IsActive));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsActive",DbType.Boolean,DBNull.Value));
				if (oNews.IsRemove.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsRemove",DbType.Boolean,oNews.IsRemove));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsRemove",DbType.Boolean,DBNull.Value));
				if (oNews.ImgPath!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ImgPath",DbType.String,oNews.ImgPath));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ImgPath",DbType.String,DBNull.Value));
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@IsNews", DbType.Boolean, oNews.IsNews));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Id",DbType.Int32,oNews.Id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveNews(News oNews)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETENews",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Id",DbType.Int32,oNews.Id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveNews(int Id)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETENews",CommandType.StoredProcedure);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Id",DbType.Int32,Id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
	}
}
